#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class NewsLetterDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new NewsLetter instance filled with the DataReader's current record data 
	     /// </summary>
	     private NewsLetter GetNewsLetterFromReader(IDataReader dr)
	     {
		     NewsLetter obj = new NewsLetter();
		     obj.NewsLetterID = (dr["NewsLetterID"] is DBNull) ? Int32.MinValue :  (Int32)dr["NewsLetterID"];
		     obj.UserID = (dr["UserID"] is DBNull) ? String.Empty :  (String)dr["UserID"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["AddedDate"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.FirstName = (dr["FirstName"] is DBNull) ? String.Empty :  (String)dr["FirstName"];
		     obj.LastName = (dr["LastName"] is DBNull) ? String.Empty :  (String)dr["LastName"];
		     obj.Email = (dr["Email"] is DBNull) ? String.Empty :  (String)dr["Email"];
		     obj.Company = (dr["Company"] is DBNull) ? String.Empty :  (String)dr["Company"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of NewsLetter with the data read from the input DataReader
	     /// </summary>
	     private List<NewsLetter> GetNewsLetterCollectionFromReader(IDataReader dr)
	     {
		     List<NewsLetter> obj = new List<NewsLetter>();
		     while(dr.Read())
			     obj.Add(GetNewsLetterFromReader(dr));
		     return obj;
	     }

         public Boolean DeleteByEmail(String Email)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Job_NewsLetter_DeleteByEmail", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


         public Boolean CheckEmail(String Email)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 try
                 {
                     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_CheckEmail", myConn);
                     myCommand.CommandType = CommandType.StoredProcedure;
                     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
                     string value = (String)myCommand.ExecuteScalar();
                     if (value.Trim().Length > 2)
                         return true;
                     return false;
                 }
                 catch
                 {
                     return false;
                 }
             }
         }

	     public int Insert(NewsLetter obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = obj.UserID;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
		     myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@NewsLetterID"].Value; 
		     }
	     }

	     public bool Update(NewsLetter obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = obj.NewsLetterID;
		     myCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = obj.UserID;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
		     myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 NewsLetterID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = NewsLetterID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the Job_NewsLetter with the specified ID
	     /// </summary>
	     public NewsLetter GetNewsLetterByID(Int32 NewsLetterID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_GetNewsLetterByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = NewsLetterID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetNewsLetterFromReader(dr) : null);
		     }
	     }

	     //Get All NewsLetter
	     public List<NewsLetter> GetAllNewsLetter(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_GetAllNewsLetter", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetNewsLetterCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  NewsLetter
	     public int GetCountNewsLetter()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Job_NewsLetter_GetCountNewsLetter", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }



	 }
}
